package Jsontest.json;

import javax.json.bind.Jsonb;
import javax.json.bind.JsonbBuilder;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDate;
import java.util.stream.Collectors;

/**
 * @author Sai
 * Created by Sai on 2019-03-15.
 */
@WebServlet(urlPatterns = {"/Jsontest/json"})
public class JsonServlet extends HttpServlet {

    // curl -v http://localhost:8080/webapp-demo/json
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {

        // 创建一个简单的Person对象,用作演示
        StudentDTO studentDTO = new StudentDTO("201641413213", "吴宇信", "网络工程二班", "男","学生",
                 21);

        // 要获取对象的JSON表示，我们需要使用JsonbBuilder类及其toJson（）方法。
        Jsonb jsonb = JsonbBuilder.create();
        String jsonPerson = jsonb.toJson(studentDTO);

        resp.setContentType("application/Jsontest.json");
        resp.setCharacterEncoding("UTF-8");

        resp.getWriter().println(jsonPerson);
    }


    // 获取前端post提交的json字符串，并转换为对象；再把对象转为json字符串，生成响应返回前端。
    // curl -v -H "Content-Type: application/Jsontest.json" http://localhost:8080/webapp-demo/json -X POST --data '{"姓名":"黎志雄","工号":"2003066","注册日期":"15-03-2019","薪水":"1000.0","身份":"老师","邮件":"lizhx@dgut.edu.cn","部门":"网络空间安全 学院"}'
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        Jsonb jsonb = JsonbBuilder.create();
        String postJsonString = new BufferedReader(new InputStreamReader(req.getInputStream()))
                                                            .lines().collect(Collectors.joining(System.lineSeparator()));
        postJsonString = new String(postJsonString.getBytes("gbk"),"utf-8");
        // 利用jsonb把json字符串转换为对象
        StudentDTO studentDTO = jsonb.fromJson(postJsonString, StudentDTO.class);
//        PersonDTO personDTO = jsonb.fromJson("{\"姓名\":\"黎志雄\"}", PersonDTO.class);

        resp.setContentType("application/Jsontest.json");
        resp.setCharacterEncoding("UTF-8");

        try (Connection conn = DriverManager.getConnection("jdbc:h2:~/test;MODE=MYSQL;DB_CLOSE_DELAY=-1", "sa", "sa")) {
    /*
        注意，Connection预设为自动“认可/提交”（Commit），也就是Statement执行SQL叙述完后，马上对数据库进行操作变更.
        如果想要对Statement要执行的SQL进行除错，可以使用setAutoCommit(false)来将自动认可取消，在执行完SQL之后，再呼叫Connection的commit()方法认可变更，
        使用Connection的getAutoCommit()可以测试是否设定为自动认可。
        不过无论是否有无执行commit()方法，只要SQL没有错，在关闭Statement或Connection前，都会执行认可commit动作，对数据库进行变更。
    */


            //3.通过数据库的连接操作数据库，实现增删改查
            Statement stmt = conn.createStatement();

            // 创建一个表
            // 如果表不存在就建立这个表，那么可以直接用 create table if not exists tablename 这样的指令来建立，不需要先去查询表是否存在。
            // 从模板表创建表：create table if not exists like old_table_name;
            stmt.executeUpdate(
                    "CREATE TABLE IF NOT EXISTS `user` (" +
                            "  `id` int(100) unsigned NOT NULL AUTO_INCREMENT," +
                            "  `name` varchar(100) NOT NULL," +
                            "  `username` varchar(100) NOT NULL," +
                            "  `myClass` varchar(100) NOT NULL," +
                            "  `sex` varchar(100) NOT NULL," +
                            "  `identity` varchar(100) NOT NULL," +
                            "  `age` int (100) NOT NULL," +
                            "  PRIMARY KEY (`id`)" +
                            ");"
            );
            PreparedStatement preparedStatement = conn.prepareStatement("insert into `user` (name,username,myClass,sex,identity,age) values (?,?,?,?,?,?);");
            preparedStatement.setNString(1, studentDTO.getName());
            preparedStatement.setNString(2, studentDTO.getUsername());
            preparedStatement.setNString(3, studentDTO.getMyClass());
            preparedStatement.setNString(4, studentDTO.getSex());
            preparedStatement.setNString(5, studentDTO.getIdentity());
            preparedStatement.setInt(6, studentDTO.getAge());


            int flag =  preparedStatement.executeUpdate();
            ResultSet rs = stmt.executeQuery("select * from user");//选择import java.sql.ResultSet;

            // 遍历每行记录
            while (rs.next()) {
                //如果结果集中有数据，就会循环打印出来
                System.out.println(rs.getInt("id") + "," + rs.getString("name") + ","
                        + rs.getString("username")+rs.getString("myClass") + ","
                        + rs.getString("sex")+ rs.getString("identity") + rs.getString("age"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // 把获取的字符串原样返回
        String jsonPerson = jsonb.toJson(studentDTO);

        resp.setContentType("application/Jsontest.json");
        resp.setCharacterEncoding("UTF-8");

        resp.getWriter().println(jsonPerson);

        String result = "{\"code\":0,\"msg\":\"操作成功\"}";
        resp.getWriter().println(result);
    }
}
